﻿// ***********************************************************
// 组织机构的数据访问类
// Creator:yehui  Date:2013-03-19
// ***********************************************************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VipSys.SystemEntity;
using System.Data;
using VipSys.Common.Library;
using System.Diagnostics;

namespace VipSys.DataLayer
{
    public class OrgnizationAccess
    {
        private static OrgnizationAccess m_Instance = null;
        /// <summary>
        /// 单实例
        /// </summary>
        public static OrgnizationAccess Instance
        {
            get
            {
                if (m_Instance == null)
                    m_Instance = new OrgnizationAccess();
                return m_Instance;
            }
        }

        public short Add(Orgnization orgnization)
        {
            string szFiled = string.Format("{0},{1},{2},{3}"
                , SystemData.OrgnizationTable.Description
                , SystemData.OrgnizationTable.OrgName
                , SystemData.OrgnizationTable.ParentID
                , SystemData.OrgnizationTable.RoleType);
            string szValue = string.Format("'{0}','{1}','{2}','{3}'"
                , orgnization.Describe
                , orgnization.OrgName
                , orgnization.ParentID
                , orgnization.RoleType);
            string szSql = string.Format("Insert Into {0}({1}) values({2})"
                , SystemData.DBTable.ORGNIZATION
                , szFiled
                , szValue);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short Edit(Orgnization orgnization)
        {
            string szFiledValue = string.Format("{0}='{1}',{2}='{3}',{4}='{5}',{6}='{7}'"
                , SystemData.OrgnizationTable.Description, orgnization.Describe
                , SystemData.OrgnizationTable.OrgName, orgnization.OrgName
                , SystemData.OrgnizationTable.ParentID, orgnization.ParentID
                , SystemData.OrgnizationTable.RoleType, orgnization.RoleType
                );
            string szCondition = string.Format(" {0}={1}"
                , SystemData.OrgnizationTable.ID, orgnization.ID);
            string szSql = string.Format("update {0} set {1} where {2}"
                , SystemData.DBTable.ORGNIZATION
                , szFiledValue
                , szCondition);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short Del(Orgnization orgnization)
        {
            string szCondition = string.Format(" {0}={1}"
                , SystemData.EmployeeTable.ID, orgnization.ID);
            string szSql = string.Format("Delete from {0}  where {1}"
                , SystemData.DBTable.ORGNIZATION
                , szCondition);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetOrgnizationPageList(Orgnization query, ref List<Orgnization> lstOrgnization)
        {
            if (lstOrgnization == null)
                lstOrgnization = new List<Orgnization>();
            lstOrgnization.Clear();
            StringBuilder sbSql=new StringBuilder();
            string szCondition = string.Format(" 1=1");
            if(!string.IsNullOrEmpty(query.OrgName))
                szCondition=string.Format(" {0} and {1} like '%{2}%'"
                    , szCondition
                    , SystemData.OrgnizationTable.OrgName,query.OrgName);
            if (!string.IsNullOrEmpty(query.RoleType))
                szCondition = string.Format(" {0} and {1}='{2}'"
                    , szCondition
                    , SystemData.OrgnizationTable.RoleType, query.RoleType);
            
            sbSql.AppendFormat("SELECT TOP {0} * FROM {1} where {2}"
                , query.Limit
                , SystemData.DBTable.ORGNIZATION
                , szCondition
                );
            if (query.Start != "0")
            {
                sbSql.Append(" and (ID <");
                sbSql.AppendFormat(" (SELECT min(ID) FROM (SELECT TOP {0} id  FROM {1} where {2} ORDER BY ID Desc) AS T)"
                    , query.Start
                    , SystemData.DBTable.ORGNIZATION
                    , szCondition
                    );
                sbSql.Append(" )");
            }
            sbSql.Append(" ORDER BY ID Desc");
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(sbSql.ToString());
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Orgnization orgnization = new Orgnization();
                    orgnization.Describe = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.Description].ToString();
                    orgnization.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.OrgnizationTable.ID].ToString());
                    orgnization.OrgName = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.OrgName].ToString();
                    orgnization.ParentID = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.ParentID].ToString();
                    orgnization.RoleType = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.RoleType].ToString();
                    lstOrgnization.Add(orgnization);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        public short GetTotalCount(Orgnization query, ref int totalCount)
        {
            string szCondition = string.Format(" 1=1");
            if (!string.IsNullOrEmpty(query.OrgName))
                szCondition = string.Format(" {0} and {1} like '%{2}%'"
                     , szCondition
                     , SystemData.OrgnizationTable.OrgName, query.OrgName);
            if (!string.IsNullOrEmpty(query.RoleType))
                szCondition = string.Format(" {0} and {1}='{2}'"
                    , szCondition
                    , SystemData.OrgnizationTable.RoleType, query.RoleType);
            string szSql = string.Format("select count(*) from {0} where {1}"
                , SystemData.DBTable.ORGNIZATION
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                totalCount = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                return SystemData.ReturnValue.OK; 
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetOrgnization(Orgnization query, ref Orgnization orgnization)
        {
            string szField = string.Format("{0},{1},{2},{3},{4}"
                , SystemData.OrgnizationTable.Description
                , SystemData.OrgnizationTable.ID
                , SystemData.OrgnizationTable.OrgName
                , SystemData.OrgnizationTable.ParentID
                , SystemData.OrgnizationTable.RoleType);
            string szCondition = string.Format("1=1");
            if(query.ID!=0)
                szCondition = string.Format("{0} and {1}={2}"
                    , szCondition
                    , SystemData.OrgnizationTable.ID, query.ID.ToString());
            if(!string.IsNullOrEmpty(query.OrgName))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.OrgnizationTable.OrgName, query.OrgName.ToString());
            string szSql = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.ORGNIZATION
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
                    return SystemData.ReturnValue.RES_NO_FOUND;
                orgnization.Describe = ds.Tables[0].Rows[0][SystemData.OrgnizationTable.Description].ToString();
                orgnization.ID = int.Parse(ds.Tables[0].Rows[0][SystemData.OrgnizationTable.ID].ToString());
                orgnization.OrgName = ds.Tables[0].Rows[0][SystemData.OrgnizationTable.OrgName].ToString();
                orgnization.ParentID = ds.Tables[0].Rows[0][SystemData.OrgnizationTable.ParentID].ToString();
                orgnization.RoleType = ds.Tables[0].Rows[0][SystemData.OrgnizationTable.RoleType].ToString();
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetOrgnizationList(Orgnization query, ref List<Orgnization> lstOrgnization)
        {
            if (lstOrgnization == null)
                lstOrgnization = new List<Orgnization>();
            lstOrgnization.Clear();
            string szField = string.Format("{0},{1},{2},{3},{4}"
                , SystemData.OrgnizationTable.Description
                , SystemData.OrgnizationTable.ID
                , SystemData.OrgnizationTable.OrgName
                , SystemData.OrgnizationTable.ParentID
                , SystemData.OrgnizationTable.RoleType);
            string szCondition=string.Format("1=1");
            if (!string.IsNullOrEmpty(query.ParentID))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.OrgnizationTable.ParentID
                    , query.ParentID);
            if (!string.IsNullOrEmpty(query.RoleType))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.OrgnizationTable.RoleType
                    , query.RoleType);
            string szSql=string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.ORGNIZATION
                , szCondition);

            try
            {
                DataSet ds=SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Orgnization orgnization = new Orgnization();
                    orgnization.Describe = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.Description].ToString();
                    orgnization.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.OrgnizationTable.ID].ToString());
                    orgnization.OrgName = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.OrgName].ToString();
                    orgnization.ParentID = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.ParentID].ToString();
                    orgnization.RoleType = ds.Tables[0].Rows[i][SystemData.OrgnizationTable.RoleType].ToString();
                    lstOrgnization.Add(orgnization);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

    }
}
